﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace JiaBao.Services.StoreService
{
    public class StoreService : IStoreService
    {
        public DataTable GetByHt(string khbs)
        {
            try
            {
                var sql = "select c.拍卖会名称,b.合同编号,b.委托方姓名,a.图录号,a.名称,a.成交价,a.佣金比率,a.佣金,a.卖家结算状况,a.卖家税后小计,a.拍卖会id from Storeall as a" +
                       " left join SalerAll as b on a.拍卖会id = b.拍卖会id and a.合同编号 = b.合同编号" +
                       " left join AuctionAll as c on a.拍卖会id = c.拍卖会id" +
                       " where b.客户标识Z =@khbz" +
                       " order by c.拍卖会id desc,b.合同编号,a.图录号";
                return SqlHelper.ExecuteDataTable(sql, new SqlParameter("khbz", khbs));
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public DataTable GetByHtAndAucId(string khbs, int aucid)
        {
            try
            {
                if (-100 == aucid)
                    return GetByHt(khbs);
                else
                {
                    var sql = "select c.拍卖会名称,b.合同编号,b.委托方姓名,a.图录号,a.名称,a.成交价,a.佣金比率,a.佣金,a.卖家结算状况,a.卖家税后小计,a.拍卖会id from Storeall as a" +
                            " left join SalerAll as b on a.拍卖会id = b.拍卖会id and a.合同编号 = b.合同编号" +
                            " left join AuctionAll as c on a.拍卖会id = c.拍卖会id" +
                            " where b.客户标识Z =@khbz and a.拍卖会id = @aucid" +
                            " order by c.拍卖会id desc,b.合同编号,a.图录号";
                    return SqlHelper.ExecuteDataTable(sql, new SqlParameter("khbz", khbs), new SqlParameter("aucid", aucid));

                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public DataTable GetByJtdj(string khbs)
        {
            try
            {
                var sql = "select c.拍卖会名称,b.竞投牌号,b.姓名,a.图录号,a.名称,a.成交价,a.酬金比率,a.酬金,a.买家结算状况,a.买家总计,a.拍卖会id from Storeall as a" +
                      " left join JtdjAll as b on a.拍卖会id = b.拍卖会id and a.竞投牌号 = b.竞投牌号" +
                      " left join AuctionAll as c on a.拍卖会id = c.拍卖会id" +
                      " where b.客户标识Z = @khbz" +
                      " order by c.拍卖会id desc,b.竞投牌号,a.图录号";
                return SqlHelper.ExecuteDataTable(sql, new SqlParameter("khbz", khbs));
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public DataTable GetByJtdjAndAucId(string khbs, int aucid)
        {
            try
            {
                if (-100 == aucid)
                    return GetByJtdj(khbs);
                else
                {
                    var sql = "select c.拍卖会名称,b.竞投牌号,b.姓名,a.图录号,a.名称,a.成交价,a.酬金比率,a.酬金,a.买家结算状况,a.买家总计,a.拍卖会id from Storeall as a" +
                            " left join JtdjAll as b on a.拍卖会id = b.拍卖会id and a.竞投牌号 = b.竞投牌号" +
                            " left join AuctionAll as c on a.拍卖会id = c.拍卖会id" +
                            " where b.客户标识Z = @khbz and a.拍卖会id=@aucid" +
                            " order by c.拍卖会id desc,b.竞投牌号,a.图录号";
                    return SqlHelper.ExecuteDataTable(sql, new SqlParameter("khbz", khbs), new SqlParameter("aucid", aucid));

                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public int UpdateZT(int aucid, int tol)
        {
            var r = (int)SqlHelper.ExecuteScalar("select count(*) from storeall where 拍卖会id= @aucid and  图录号= @tol", new SqlParameter("aucid", aucid), new SqlParameter("tol", tol));
            if (r > 1)
                return r;
            var sql = "update Storeall set 买家结算状况= 'ZT' where 拍卖会id= @aucid and  图录号= @tol";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("aucid", aucid), new SqlParameter("tol", tol));
        }
    }
}
